create or replace package DM_KHO_MA_THE_IN_P is
/*
|| purpose: basic rutines for table DM_KHO_MA_THE_IN
||
|| created by: tinhth
*/

/*insert one record into Db, no commit*/
procedure insert_dm_kho_ma_the_in (
		op_id out DM_KHO_MA_THE_IN.ID%TYPE
		,ip_ma_kich_hoat in DM_KHO_MA_THE_IN.MA_KICH_HOAT%TYPE
		,ip_so_serial in DM_KHO_MA_THE_IN.SO_SERIAL%TYPE
		,ip_loai_thoi_han in DM_KHO_MA_THE_IN.LOAI_THOI_HAN%TYPE
		,ip_han_su_dung in DM_KHO_MA_THE_IN.HAN_SU_DUNG%TYPE
		,ip_loai_the in DM_KHO_MA_THE_IN.LOAI_THE%TYPE
		,ip_da_kich_hoat_yn in DM_KHO_MA_THE_IN.DA_KICH_HOAT_YN%TYPE
		);
/*delete using ID, no commit*/
procedure delete_dm_kho_ma_the_in (
		ip_id DM_KHO_MA_THE_IN.ID%TYPE
		);
/*update entire record, no commit*/
procedure update_dm_kho_ma_the_in (
		 ip_id in DM_KHO_MA_THE_IN.ID%TYPE
		,ip_ma_kich_hoat in DM_KHO_MA_THE_IN.MA_KICH_HOAT%TYPE
		,ip_so_serial in DM_KHO_MA_THE_IN.SO_SERIAL%TYPE
		,ip_loai_thoi_han in DM_KHO_MA_THE_IN.LOAI_THOI_HAN%TYPE
		,ip_han_su_dung in DM_KHO_MA_THE_IN.HAN_SU_DUNG%TYPE
		,ip_loai_the in DM_KHO_MA_THE_IN.LOAI_THE%TYPE
		,ip_da_kich_hoat_yn in DM_KHO_MA_THE_IN.DA_KICH_HOAT_YN%TYPE
		);
/*
|| Tao the in kihc hoat hang loat
*/
procedure tao_the_in_kich_hoat(ip_so_luong_the number
                                              ,ip_loai_the in DM_KHO_MA_THE_IN.LOAI_THE%TYPE
                                              ,ip_loai_thoi_han in DM_KHO_MA_THE_IN.LOAI_THOI_HAN%TYPE
                                              ,ip_han_su_dung in DM_KHO_MA_THE_IN.HAN_SU_DUNG%TYPE);
/*
|| Kiem tra ma kich hoat co hop le khong?
*/                                       
procedure lay_trang_thai_the(ip_ma_kich_hoat in DM_KHO_MA_THE_IN.MA_KICH_HOAT%TYPE
                                             ,op_status_the out varchar2);
/*
|| sinh serial loai thoi han 1 thang
*/
function tao_so_serial_1_thang return varchar2;
/*
|| cap nhat the kich hoat thanh cong
*/ 
procedure cap_nhat_trang_thai_the(ip_id in DM_KHO_MA_THE_IN.ID%TYPE
                                        ,ip_thanh_cong_yn DM_KHO_MA_THE_IN.DA_KICH_HOAT_YN%TYPE);
/*END-OF-PACKAGE*/
end DM_KHO_MA_THE_IN_P;
/
create or replace package body DM_KHO_MA_THE_IN_P is
/*
|| purpose: basic rutines for table DM_KHO_MA_THE_IN
||
|| created by: tinhth
*/

/*insert one record into Db, no commit*/
procedure insert_dm_kho_ma_the_in (
		op_id out DM_KHO_MA_THE_IN.ID%TYPE
		,ip_ma_kich_hoat in DM_KHO_MA_THE_IN.MA_KICH_HOAT%TYPE
		,ip_so_serial in DM_KHO_MA_THE_IN.SO_SERIAL%TYPE
		,ip_loai_thoi_han in DM_KHO_MA_THE_IN.LOAI_THOI_HAN%TYPE
		,ip_han_su_dung in DM_KHO_MA_THE_IN.HAN_SU_DUNG%TYPE
		,ip_loai_the in DM_KHO_MA_THE_IN.LOAI_THE%TYPE
		,ip_da_kich_hoat_yn in DM_KHO_MA_THE_IN.DA_KICH_HOAT_YN%TYPE
		)  is
begin
	op_id := db_utils_p.get_object_id();
	insert into DM_KHO_MA_THE_IN (
		 ID
		,MA_KICH_HOAT
		,SO_SERIAL
		,LOAI_THOI_HAN
		,HAN_SU_DUNG
		,LOAI_THE
		,DA_KICH_HOAT_YN
	 )
	values (
		 op_id
		,ip_ma_kich_hoat
		,ip_so_serial
		,ip_loai_thoi_han
		,ip_han_su_dung
		,ip_loai_the
		,ip_da_kich_hoat_yn
	 );
end insert_dm_kho_ma_the_in;
/*
|| delete using ID, no commit
*/
procedure delete_dm_kho_ma_the_in (
		ip_id DM_KHO_MA_THE_IN.ID%TYPE
		) is
begin
	delete from DM_KHO_MA_THE_IN where ID = ip_id;
end delete_dm_kho_ma_the_in;
/*
|| update entire record, no commit
*/
procedure update_dm_kho_ma_the_in (
		 ip_id in DM_KHO_MA_THE_IN.ID%TYPE
		,ip_ma_kich_hoat in DM_KHO_MA_THE_IN.MA_KICH_HOAT%TYPE
		,ip_so_serial in DM_KHO_MA_THE_IN.SO_SERIAL%TYPE
		,ip_loai_thoi_han in DM_KHO_MA_THE_IN.LOAI_THOI_HAN%TYPE
		,ip_han_su_dung in DM_KHO_MA_THE_IN.HAN_SU_DUNG%TYPE
		,ip_loai_the in DM_KHO_MA_THE_IN.LOAI_THE%TYPE
		,ip_da_kich_hoat_yn in DM_KHO_MA_THE_IN.DA_KICH_HOAT_YN%TYPE
		) is
begin
	update DM_KHO_MA_THE_IN
	 set
		 MA_KICH_HOAT = ip_ma_kich_hoat
		,SO_SERIAL = ip_so_serial
		,LOAI_THOI_HAN = ip_loai_thoi_han
		,HAN_SU_DUNG = ip_han_su_dung
		,LOAI_THE = ip_loai_the
		,DA_KICH_HOAT_YN = ip_da_kich_hoat_yn
	where ID = ip_id;
end update_dm_kho_ma_the_in;
/*
|| sinh serial loai thoi han 1 thang
*/
function tao_so_serial_1_thang return varchar2 is
    v_serial_max varchar2(50):='';
    v_dc_stt number(8);
begin
      select max(d.so_serial)
             into v_serial_max
      from dm_kho_ma_the_in d 
      where d.loai_thoi_han = constants_p.C_THE_1_THANG;
        
     if v_serial_max is null then
        v_dc_stt :=1;
     else
        v_dc_stt := to_number(substr(v_serial_max,5,8)) + 1;
     end if;
     return 'ACM-'|| Lpad(v_dc_stt,8,0);
end tao_so_serial_1_thang;
/*
|| sinh serial loai thoi han 3 thang
*/
function tao_so_serial_3_thang return varchar2 is
    v_serial_max varchar2(50):='';
    v_dc_stt number(8);
begin
      select max(d.so_serial)
             into v_serial_max
      from dm_kho_ma_the_in d 
      where d.loai_thoi_han = constants_p.C_THE_3_THANG;
        
     if v_serial_max is null then
        v_dc_stt :=1;
     else
        v_dc_stt := to_number(substr(v_serial_max,5,8)) + 1;
     end if;
     return 'ACB-'|| Lpad(v_dc_stt,8,0);
end tao_so_serial_3_thang;
/*
|| sinh serial loai thoi han 6 thang
*/
function tao_so_serial_6_thang return varchar2 is
    v_serial_max varchar2(50):='';
    v_dc_stt number(8);
begin
      select max(d.so_serial)
             into v_serial_max
      from dm_kho_ma_the_in d 
      where d.loai_thoi_han = constants_p.C_THE_6_THANG;
        
     if v_serial_max is null then
        v_dc_stt :=1;
     else
        v_dc_stt := to_number(substr(v_serial_max,5,8)) + 1;
     end if;
     return 'ACS-'|| Lpad(v_dc_stt,8,0);
end tao_so_serial_6_thang;
/*
|| sinh serial loai thoi han 1 thang
*/
function tao_so_serial_12_thang return varchar2 is
    v_serial_max varchar2(50):='';
    v_dc_stt number(8);
begin
      select max(d.so_serial)
             into v_serial_max
      from dm_kho_ma_the_in d 
      where d.loai_thoi_han = constants_p.C_THE_12_THANG;
        
     if v_serial_max is null then
        v_dc_stt :=1;
     else
        v_dc_stt := to_number(substr(v_serial_max,5,8)) + 1;
     end if;
     return 'ACX-'|| Lpad(v_dc_stt,8,0);
end tao_so_serial_12_thang;
/*
|| Tao the in kihc hoat hang loat
*/
procedure tao_the_in_kich_hoat(ip_so_luong_the number
                                              ,ip_loai_the in DM_KHO_MA_THE_IN.LOAI_THE%TYPE
                                              ,ip_loai_thoi_han in DM_KHO_MA_THE_IN.LOAI_THOI_HAN%TYPE
                                              ,ip_han_su_dung in DM_KHO_MA_THE_IN.HAN_SU_DUNG%TYPE)
is
v_num_sl_the_chua_in number :=0;
op_id number(18,0) :=0;
begin
     -- kiem tra xem co du so luong trong kho khong?
     select
           count(g.id) into v_num_sl_the_chua_in
     from dm_kho_ma_the_goc g
     where g.loai_the = ip_loai_the
           and g.loai_thoi_han = ip_loai_thoi_han
           and g.da_in_yn = 'N';
     if v_num_sl_the_chua_in < ip_so_luong_the then
        raise_application_error(-20001,'SO_LUONG_THE CAN IN VUOT QUA SO_LUONG_THE TRONG KHO CHUA IN ('
                                                     || v_num_sl_the_chua_in
                                                     ||')') ;
     end if;
     -- insert kho the in
     if ip_loai_thoi_han = constants_p.C_THE_1_THANG then
       for v_rec_the in (select t.id, t.ma_the_in_kich_hoat from dm_kho_ma_the_goc t where t.loai_the = ip_loai_the
                                  and t.loai_thoi_han = constants_p.C_THE_1_THANG
                                  and t.da_in_yn = 'N'
                                  and rownum <= ip_so_luong_the)
       loop
           --
           insert_dm_kho_ma_the_in(op_id => op_id
                                         ,ip_ma_kich_hoat => v_rec_the.ma_the_in_kich_hoat
                                         ,ip_so_serial => tao_so_serial_1_thang
                                         ,ip_loai_thoi_han => constants_p.C_THE_1_THANG
                                         ,ip_loai_the => ip_loai_the
                                         ,ip_han_su_dung => ip_han_su_dung
                                         ,ip_da_kich_hoat_yn => 'N');
          --
          DM_KHO_MA_THE_GOC_P.update_ma_the_goc_da_in(v_rec_the.id);
       end loop;
     else
         if ip_loai_thoi_han = constants_p.C_THE_3_THANG then
           for v_rec_the in (select t.id, t.ma_the_in_kich_hoat from dm_kho_ma_the_goc t where t.loai_the = ip_loai_the
                                      and t.loai_thoi_han = constants_p.C_THE_3_THANG 
                                      and t.da_in_yn = 'N'
                                      and rownum <= ip_so_luong_the)
           loop
               insert_dm_kho_ma_the_in(op_id => op_id
                                         ,ip_ma_kich_hoat => v_rec_the.ma_the_in_kich_hoat
                                         ,ip_so_serial => tao_so_serial_3_thang
                                         ,ip_loai_thoi_han => constants_p.C_THE_3_THANG
                                         ,ip_loai_the => ip_loai_the
                                         ,ip_han_su_dung => ip_han_su_dung
                                         ,ip_da_kich_hoat_yn => 'N');               
              --
              DM_KHO_MA_THE_GOC_P.update_ma_the_goc_da_in(v_rec_the.id);                          
           end loop;
      else
          if ip_loai_thoi_han = constants_p.C_THE_6_THANG then
             for v_rec_the in (select t.id, t.ma_the_in_kich_hoat from dm_kho_ma_the_goc t where t.loai_the = ip_loai_the
                                        and t.loai_thoi_han = constants_p.C_THE_6_THANG 
                                        and t.da_in_yn = 'N'
                                        and rownum <= ip_so_luong_the)
             loop
                 insert_dm_kho_ma_the_in(op_id => op_id
                                         ,ip_ma_kich_hoat => v_rec_the.ma_the_in_kich_hoat
                                         ,ip_so_serial => tao_so_serial_6_thang
                                         ,ip_loai_thoi_han => constants_p.C_THE_6_THANG
                                         ,ip_loai_the => ip_loai_the
                                         ,ip_han_su_dung => ip_han_su_dung
                                         ,ip_da_kich_hoat_yn => 'N');
                 --
                 DM_KHO_MA_THE_GOC_P.update_ma_the_goc_da_in(v_rec_the.id);
             end loop;
      else
          if ip_loai_thoi_han = constants_p.C_THE_12_THANG then
             for v_rec_the in (select t.id, t.ma_the_in_kich_hoat from dm_kho_ma_the_goc t where t.loai_the = ip_loai_the
                                        and t.loai_thoi_han = constants_p.C_THE_12_THANG
                                        and t.da_in_yn = 'N'
                                        and rownum <= ip_so_luong_the)
             loop
                 insert_dm_kho_ma_the_in(op_id => op_id
                                         ,ip_ma_kich_hoat => v_rec_the.ma_the_in_kich_hoat
                                         ,ip_so_serial => tao_so_serial_12_thang
                                         ,ip_loai_thoi_han => constants_p.C_THE_12_THANG
                                         ,ip_loai_the => ip_loai_the
                                         ,ip_han_su_dung => ip_han_su_dung
                                         ,ip_da_kich_hoat_yn => 'N');
                --
                DM_KHO_MA_THE_GOC_P.update_ma_the_goc_da_in(v_rec_the.id);                         
             end loop;
      end if;
      end if;
      end if;
      end if;
end tao_the_in_kich_hoat;
/*
|| Kiem tra ma kich hoat co hop le khong?
*/                                       
procedure lay_trang_thai_the(ip_ma_kich_hoat in DM_KHO_MA_THE_IN.MA_KICH_HOAT%TYPE
                                             ,op_status_the out varchar2)
is
v_trang_thai_the_yn varchar2(1):= null;
v_bol_co_ma_the boolean := false;
cursor v_the_cur is
     select g.da_kich_hoat_yn from dm_kho_ma_the_in g
     where g.ma_kich_hoat = ip_ma_kich_hoat;

begin
     -- co the nay khong?
     open v_the_cur;
     fetch v_the_cur into v_trang_thai_the_yn;
     v_bol_co_ma_the := v_the_cur%found;
     close v_the_cur;
     if not v_bol_co_ma_the then
        op_status_the := constants_p.C_MESS_MA_THE_KHONG_DUNG;
        return;
     end if;
     -- tra ket qua
     if v_trang_thai_the_yn = 'Y' then
        op_status_the := constants_p.C_MESS_MA_THE_DA_SU_DUNG;    
     else
        op_status_the := constants_p.C_MESS_MA_THE_CHUA_SU_DUNG;
      end if;
end lay_trang_thai_the;
/*
|| cap nhat the kich hoat thanh cong
*/ 
procedure cap_nhat_trang_thai_the(ip_id in DM_KHO_MA_THE_IN.ID%TYPE
                                        ,ip_thanh_cong_yn DM_KHO_MA_THE_IN.DA_KICH_HOAT_YN%TYPE)
is
begin
     update dm_kho_ma_the_in t
     set t.da_kich_hoat_yn = ip_thanh_cong_yn
     where t.id = ip_id;
end cap_nhat_trang_thai_the;
/*END-OF-PACKAGE*/
end DM_KHO_MA_THE_IN_P;
/
